from datetime import datetime

import pandas as pd
import paho.mqtt.client as mqtt
import json
import time

'''
发送excel里面的数据到mqtt，根据loop_count的配置来确认是否循环读取
'''
class EexcelPublish:
    def __init__(self,mqtt_server,mqtt_port,mqtt_topic,excel_filename,mqtt_user=None,mqtt_passwd=None):
        self.mqtt_server = mqtt_server
        self.mqtt_port = mqtt_port
        self.mqtt_user = mqtt_user
        self.mqtt_passwd = mqtt_passwd
        self.mqtt_topic = mqtt_topic
        self.excel_filename = excel_filename
        self.client = mqtt.Client()  # 创建MQTT客户端实例

    def on_connect(self, client, userdata, flags, rc):
        #MQTT连接成功的回调函数
        print("Connected with result code " + str(rc))

    def connect_to_mqtt(self):
        #连接到MQTT服务器
        self.client.on_connect = self.on_connect
        self.client.connect(self.mqtt_server, self.mqtt_port, 60)
        self.client.username_pw_set(self.mqtt_user,self.mqtt_passwd)
        self.client.loop_start()

    def disconnect_from_mqtt(self):
        #断开MQTT连接
        self.client.loop_stop()
        self.client.disconnect()

    def read_excel_data(self):
        """
        读取Excel文件中的数据
        """
        try:
            df = pd.read_excel(self.excel_filename)
            print("Excel data read successfully")
            return df
        except Exception as e:
            print(f"Error reading Excel file: {e}")
            return None

    def add_time_column(self, df):
        # 添加'updateTime'列并用当前时间填充到毫秒
        df['updateTime'] = datetime.now().isoformat(sep=' ', timespec='milliseconds')
        print("Added 'updateTime' column with current time")
        return df

    def send_data_to_mqtt(self, df, loop_count=None):
        """
        将数据发送到MQTT代理
        :param df: 包含数据的DataFrame
        loop_count 加了循环次数
        """
        if df is None:
            print("No data to send")
            return
        # 获取列名
        columns = df.columns.tolist()
        print(f"Columns in the Excel file: {columns}")
        loop = 0
        while loop_count is None or loop < loop_count:
            for index, row in df.iterrows():
                # 动态构建数据字典
                data = {col: row[col] for col in columns}
                json_data = json.dumps(data)
                self.client.publish(self.mqtt_topic, json_data)
                print(f"Sent data: {json_data}")
                time.sleep(1)
            loop += 1
            print(f"Completed loop {loop}")

    def run(self,loop_count=None):
        """
        运行数据发送流程
        :param loop_count: 如果是None就是无线循环
        """
        self.connect_to_mqtt()
        df = self.read_excel_data()
        if df is not None:
            df = self.add_time_column(df)
        self.send_data_to_mqtt(df, loop_count)
        self.disconnect_from_mqtt()

if __name__ == "__main__":

    # MQTT服务器地址和端口
    MQTT_SERVER = "218.108.8.218"
    MQTT_PORT = 1883
    MQTT_TOPIC = "gw/uwb"
    user = "gwza"
    password = "gwza@2024"
    # 读取Excel文件
    excel_file = 'anchordatas.xlsx'
    sender = EexcelPublish(MQTT_SERVER, MQTT_PORT,MQTT_TOPIC,excel_file,user, password)
    sender.run(2)


